In past versions of DB2, the creation and managing of indexes for regular tables was fairly simple and straightforward, with generally only two kinds of indexes as it pertained to partitioning: namely, partitioned and non-partitioned; and, the definition and allowable attributes of each was not significantly influenced by the kind of table for which it was being created. For example, a regular non-partitioned table may optionally have a non-partitioned index that was either clustering or not clustering, but may not have a partitioned index; while, a partitioned table required, at minimum, a partitioned clustering index; and, the mere creation of an index would not cause any underlying implicit changes to the table and its definition.
With the advent of DB2 version 8, however, this has all changed. Now, creating, altering and managing indexes can be much more complex and involved; and, the mere alteration or creation of an index can have significant ramifications to the table and its definition—causing implicit changes to occur that cannot be undone without dropping and recreating the table and (quite possibly, depending on the kinds of objects,) all of its dependent objects.
Additionally, although a table may have multiple indexes, which was just as true prior to DB2 version 8 as it is today; tables may now have a considerably larger and more diverse combination of different kinds of indexes. Whereas, some new kinds of indexes may also influence the attributes and kind of index that may be subsequently added; how others may be altered; whether or not the creation of one kind over another will cause implicit changes to the table for which it is being created; and, more scenarios dictating whether or not a certain kind of an index would be valid at all.
Add to this, virtual objects or real objects with virtual changes in an RC/Migrator Alteration, Migration or Compare strategy, and the difficulty in keeping track of each change to other multiple objects and how each change will influence the (implicit or explicit) alteration or creation of still other objects (wanted or not,) the attributes that may be legally specified based on other objects, virtual or real, their changes, virtual or real, becomes a daunting, if not impossible, error-prone task if not for the invention of some new tools.
With all the new rules and regulations that are now in play and the ever increasing potential for error and surprises, it was discovered that it can be quite time consuming and frustrating trying to determine exactly what the creation of a certain kind of index, or the alteration of an existing index, would yield—not to mention the fact that the intended creation of a new index or alteration of an existing index may yield an index that is of a kind that was not wanted or an implicit (irreversible) change to the table due to an inadvertent oversight, fatigue and generally the unavoidable element of simple human error.
In light of all this, it became necessary to invent a method to facilitate the creation, alteration and general management of all these new kinds of indexes based on the kind of table object, whether the index would cause the table object to be implicitly converted to use TCP, and etc.; while at the same time minimizing, if not entirely eliminating, the element of error and guaranteeing generated DDL (Data Definition Language) statements that will not only successfully execute, but will also yield the expected changes to DB2 table and index objects with no surprises.